<?php
/**
 * Classe herdável para manipulação do banco de dados
 * Inheritable class for database manipulation
 * @author henrisk
 */
class DataBase_Model {
	/**
	 * Conexão com o banco de dados
	 * Database connection
	 * @var mysql_connection
	 */
	private $objConnection;
	/**
	 * Número de registros afetados na última execução
	 * Number of affected rows on last execution
	 * @var int
	 */
	private $intAffectedRows;
	/**
	 * Nome da tabela para efetuar alterações
	 * Name of table to affect
	 * @var string
	 */
	private $strTableName;
	/**
	 * Endereço do host do banco de dados
	 * Address of database host
	 * @var string
	 */
	private $strHost;
	/**
	 * Usuário do banco de dados
	 * User of the database
	 * @var string
	 */
	private $strLogin;
	/**
	 * Senha do banco de dados
	 * Password of the database
	 * @var password
	 */
	private $strPassword;
	/**
	 * Nome da base de dados a se conectar
	 * Database name to connect 
	 * @var string
	 */
	private $strDataBaseName;
	/**
	 * Número da para connectar
	 * Port number to connect
	 * @var int
	 */
	private $intPort;
	/**
	 * Última query/script executado
	 * Last query/script executed
	 * @var string
	 */
	private $strLastSQL;
	/**
	 * Nome dos campos da tabela
	 * Name of the table fields
	 * @var array
	 */
	private $arrFields;
	/**
	 * Nomes dos campos chave da tabela
	 * Name of the key fields of the table
	 * @var array
	 */
	private $arrKeys;
	/**
	 * Mensagem de erro da execução da query
	 * Error message on query execution
	 * @var string
	 */
	private $strErrorMessage;
	/**
	 * Código de erro da execução de comandos SQL
	 * Error code from SQL execution
	 * @var unknown_type
	 */
	private $intErrorNumber;
	
	/**
	 * Inicializa todas as variáveis da classe
	 * Initialize all class variables
	 */
	public function __construct($strHost, $strLogin, $strPassword, $strDatabaseName, $intPort) {
		$this->objConnection = false;
		$this->strTableName = false;
		$this->intAffectedRows = false;
		$this->strHost = $strHost;
		$this->strLogin = $strLogin;
		$this->strPassword = $strPassword;
		$this->strLastSQL = false;
		$this->arrFields = false;
		$this->arrKeys = false;
		$this->strErrorMessage = false;
		$this->openConnection();
	}
	
	/**
	 * Cria uma nova conexão
	 * Creates a new connection
	 * @return object
	 */
	private function openConnection() {
		$this->objConnection = new mysqli($this->strHost, $this->strLogin, $this->strPassword);
		$this->intErrorNumber = mysqli_connect_errno();
	}
	
	/**
	 * Encerra a conexão antes de destruir a classe
	 * Closes the connection before destruct the class
	 */
	private function __destruct() {
		$this->closeConnection();
	}
	
	public static function testConnection($strHost, $strLogin, $strPassword, $strDatabaseName, $intPort) {
		$objConnection = @new mysqli($strHost, $strLogin, $strPassword, $strDatabaseName, $intPort);
		$objErrorResult = new stdClass;
		$objErrorResult->strErrorMessage = mysqli_connect_error();
		$objErrorResult->intErrorNumber = mysqli_connect_errno();
		
		if($objErrorResult->intErrorNumber == 0) {
			$objConnection->close();
			return true;
		}
		else
			return $objErrorResult;
	}
	
	/**
	 * Encerra a conexão
	 * Closes the connection
	 * @return object
	 */
	private function closeConnection() {
		$this->objConnection->close();
	}
	
	/**
	 * Recupera a última query/script executada
	 * Returns the last executed query/script
	 * @return string
	 */
	public function getLastSQL() {
		return $this->strLastSQL;
	}
	
	
	/**
	 * Nome da tabela
	 * Name of the table
	 * @return string
	 */
	public function getTableName() {
		return $this->strTableName;
	}
	
	
	/**
	 * Recupera o número de linhas afetadas na última execução
	 * Returns the of affected rws on last execution
	 * @return int
	 */
	public function getAffectedRows() {
		return $this->intAffectedRows;
	}
	
	
	/**
	 * Define o nome da tabela
	 * Set the table name
	 * @param string $strTableName
	 */
	protected function setTableName($strTableName) {
		$this->strTableName = $strTableName;
	}
	
	/**
	 * Recupera o(s) campo(s) chave da tabela
	 * Returns the key field(s) of the table
	 * @return array
	 */
	public function getKeys() {
		if($this->arrKeys === false) {
			$strSQL = 'DESC ' . $this->strTableName;
			$arrResult = $this->query($strSQL);
			$arrKeys = array();
			foreach($arrResult as $objField) {
				if($objField->Key == 'PRI')
					$arrKeys[] = $objField->Field;
			}
			
			$this->arrKeys = $arrKeys;
		}
		
		return $arrKeys;
	}
	
	
	/**
	 * Recupera o nome dos campos da tabela
	 * Returns the name of fields of the table
	 * @return array
	 */
	public function getFields() {
		if($this->arrFields === false) {
			$strSQL = 'DESC ' . $this->strTableName;
			$arrResult = $this->query($strSQL);
			$arrKeys = array();
			foreach($arrResult as $objField)
				$arrKeys[] = $objField->Field;
			
			$this->arrFields = $arrKeys;
		}
		

		return $this->arrFields;
	}
	
	
	/**
	 * Escapa uma string para que não hajam problemas no banco de dados
	 * Escapes an string
	 * @param string $string
	 * @return string
	 */
	private function escape($string) {
		if(stristr($string,'(') === false) {
			$strEscapedString = $this->objConnection->real_escape_string($string);
			$strEscapedString = "'$strEscapedString'";
		}
		else
			$strEscapedString = $string;
		
		return $strEscapedString;
	}
	
	/**
	 * Recupera a ultima mensagem de erro do item executado
	 * Returns the last error message about the last execution
	 * @return string
	 */
	public function getErrorMessage() {
		return $this->strErrorMessage;
	}
	
	/**
	 * Recupera um registro de acordo com a chave da tabela
	 * Returns a register by the table key
	 * @param array/string/int $key
	 * @return stdObject
	 */
	public function get($keyValue) {
		$arrKeys = $this->getKeys();
		if(is_array($keyValue)) {
			if(count($arrKeys) > 1) {
				foreach($arrKeys as $strIndex => $strKeyName) {
					if(array_key_exists($strKeyName, $keyValue) === true) {
						if($strWhere == "")
							$strWhere = "WHERE $strKeyName = " . $this->escape($keyValue[$strKeyName]) . "\n";
						else
							$strWhere .= " AND $strKeyName = " . $this->escape($keyValue[$strKeyName]) . "\n";
					}
				}
			}
			else
				$strWhere = "WHERE " . $arrKeys[0] . " = " . $this->escape($keyValue[0]);
			
			if(count($arrWhere) > 0) {
				$strWhere = 'WHERE ' . $strWhere;

				$strSQL = "
					SELECT
						" . implode(',', $this->getFields()) . "
					FROM
						" . $this->strTableName . "
					$strWhere
				";

				return $this->query($strSQL);
			}
		}
		else {
			throw new Exception('Parameter must be an array!');
		}
	}
	
	
	/**
	 * Recupera uma lista de acordo com os parâmetros desejados
	 * Returns a list acordding the parameters
	 * @return array
	 */
	public function loadList($arrWhere = false, $arrFields = false, $strOrderBy = false, $strLimit = false) {
		if($arrWhere === false || is_array($arrWhere)) {
			$strWhere = "";
			if($arrWhere !== false && count($arrWhere) > 0) {
				foreach($arrWhere as $strKey => $strValue) {
					if($strWhere == "")
						$strWhere = "WHERE $strKey = " . $this->escape($strValue) . "\n";
					else
						$strWhere .= " AND $strKey = " . $this->escape($strValue) . "\n";
				}
			}
						
			$strFields = ($arrFields !== false ? implode(",\n", $arrFields) : implode(",\n", $this->getFields()));
				
			if($strOrderBy !== false && stristr($strOrderBy, 'order by') === false)
				$strOrderBy = 'ORDER BY ' . $strOrderBy;
			else if($strOrderBy !== false)
				$strOrderBy = '';
				
			if($strLimit !== false && stristr($strLimit, 'limit') === false)
				$strLimit = 'limit ' . $strLimit;
			else if($strLimit !== false)
				$strLimit = '';
			
			$strSQL = "
				SELECT
					$strFields
				FROM
					$this->strTableName
				$strWhere
				$strOrderBy
				$strLimit
			";
				
			return $this->query($strSQL);
		}
		else
			throw new Exception('LoadList => arrWhere deve ser um array');
	}
	
	
	/**
	 * Insere um novo registro
	 * Inserts a new register
	 * @param array $arrData
	 * @return boolean
	 */
	public function insert($arrData) {
		$strValues = "";
		foreach($arrData as $strField => $strValue) {
			if($strValues == "")
				$strValues = "$strField = " . $this->escape($strValue);
			else
				$strValues .= ", $strField = " . $this->escape($strValue);
		}
		
		$strSQL = "
			INSERT INTO $this->strTableName
			SET
				$strValues
		";
		
		return $this->execute($strSQL);
	}
	
	
	/**
	 * Atualiza um registro
	 * Updates an register
	 * @param array $arrValues
	 * @return boolean
	 */
	public function update($arrData) {
		$strWhere = "";
		$arrKeys = $this->getKeys();
		foreach($arrData as $strFieldName => $strFieldValue) {
			if(array_key_exists($strFieldName, $arrData) === true) {
				if($strWhere == "")
					$strWhere = "WHERE $strFieldName = " . $this->escape($strFieldValue) . "\n";
				else
					$strWhere .= " AND $strFieldName = " . $this->escape($strFieldValue) . "\n";
				
				unset($arrData[$strFieldName]);
			}
		}
		
		$strValues = "";
		foreach($arrData as $strField => $strValue) {
			if($strValues == "")
				$strValues = "$strField = " . $this->escape($strValue);
			else
				$strValues .= ", $strField = " . $this->escape($strValue);
		}
		
		$strSQL = "
			UPDATE
				$this->strTableName
			SET
				$strValues
			$strWhere
		";
				
		return $this->execute($strSQL);
	}
	
	
	/**
	 * Exclui um registro
	 * Deletes an register
	 * @param array $arrKey
	 * @return boolean
	 */
	public function delete($arrValues) {
		$arrKeys = $this->getKeys();
		foreach($arrValues as $strFieldName => $strFieldValue){
			if(array_key_exists($strFieldName, $arrKeys) === false)
				unset($arrValues[$strFieldName]);
		}
		
		$strWhere = "";
		foreach($arrValues as $strKey => $strValue) {
			if($strWhere == "")
				$strWhere = "WHERE $strKey = " . $this->escape($strValue) . "\n";
			else
				$strWhere .= "AND $strKey = " . $this->escape($strValue) . "\n";
		}

		$strSQL = "
			DELETE FROM 
				$this->strTableName 
			$strWhere
		";
		
		return $this->execute($strSQL);
	}
	
	
	/**
	 * Atualiza um registro de acordo com a condição passada
	 * Updates an register acordding the passed condition
	 * @param array $arrValues
	 * @param array $arrCondition
	 * @return boolean
	 */
	public function updateByCondition($arrValues, $strCondition) {
		$strValues = "";
		foreach($arrValues as $strKey => $strValue) {
			if($strValues == "")
				$strValues = "$strKey = " . $this->escape($strValue);
			else
				$strValues .= ", $strKey = " . $this->escape($strValue);
		}
		
		$strSQL = "
			UPDATE
				$this->strTableName
			SET
				$strValues
			WHERE
				$strCondition
		";
				
		return $this->execute($strSQL);
	}
	
	
	/**
	 * Exclui um ou mais registros de acordo com uma condição
	 * Deletes an regsiter or more acordding a condition
	 * @param array $arrCondition
	 * @return boolean
	 */
	public function deleteByCondition($strCondition) {
		$strSQL = "
			DELETE FROM 
				$this->strTableName 
			WHERE 
				$strCondition
		";
		
		return $this->execute($strSQL);
	}
	
	
	/**
	 * Executa uma query
	 * Executes an query
	 * @param string $strSQL
	 * @return array
	 */
	public function query($strSQL) {
		$objResult = $this->objConnection->query($strSQL);
		$this->strErrorMessage = $this->objConnection->sqlstate();
		$this->intErrorNumber = mysqli_errno();
		$this->strLastSQL = $strSQL;
		if($objResult) {
			$arrFinalResult = $this->transformResult($objResult);
			$this->intAffectedRows = $objResult->num_rows;
			$objResult->free_result();
		}
		else {
			$arrFinalResult = false;
			$this->intAffectedRows = 0;
		}

		return $arrFinalResult;
	}
	
	/**
	 * Executa um script
	 * Executes an script
	 * @param string $strSQL
	 */
	public function execute($strSQL) {
		$objResult = $this->objConnection->query($strSQL);
		$this->strErrorMessage = $this->objConnection->sqlstate;
		$this->intErrorNumber = mysqli_errno();
		$this->strLastSQL = $strSQL;
		$this->intAffectedRows = $objResult->num_rows;
	}
	
	/**
	 * Transforma o resultado em um array de stdobjects
	 * Transform the result into an array of stdobjects
	 * @param array $arrResult
	 * @return array
	 */
	private function transformResult($objResult) {
		$arrFinalResult = array();
		while($arrRow = $objResult->fetch_assoc())
			$arrFinalResult[] = (object) $arrRow;
			
		return $arrFinalResult;
	}
}
?>